using System.Data;
using MySqlConnector;

namespace Front._11_24;

public partial class Book : Form
{
    public Book()
    {
        InitializeComponent();
    }

    private void 搜索_Click(object sender, EventArgs e)
    {
        throw new System.NotImplementedException();
    }

    private void book_Load(object sender, EventArgs e)
    {
        DetAllBooks();
        GetAllCategories();
    }

   private void GetAllCategories()
{
    using var connection = new MySqlConnection("server=localhost;port=3306;database=book;user=root;password=123456;charset=utf8mb4;AllowPublicKeyRetrieval=True;");
    connection.Open();
    string sql = "SELECT DISTINCT book_category FROM book_info;";
    
    MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(sql, connection);
    DataSet dataSet = new DataSet();
    mySqlDataAdapter.Fill(dataSet, "book_category");
    
    if (dataSet.Tables["book_category"].Rows.Count > 0)
    {
        DataTable categoryTable = dataSet.Tables["book_category"];
        
        // 插入一行全部
        DataRow row = categoryTable.NewRow();
        row[0] = "全部";
        categoryTable.Rows.InsertAt(row, 0);

        comboBox1.DataSource = categoryTable;
        comboBox1.DisplayMember = categoryTable.Columns[0].ColumnName;
        comboBox1.ValueMember = categoryTable.Columns[0].ColumnName;
        
        // 添加选择改变事件
        comboBox1.SelectedIndexChanged += ComboBox1_SelectedIndexChanged;
    }
}

// 选择分类时的事件处理方法
private void ComboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
    if (comboBox1.SelectedItem != null)
    {
        string selectedCategory = comboBox1.SelectedValue?.ToString();
        GetBooksByCategory(selectedCategory);
    }
}

// 根据分类查询图书的方法
private void GetBooksByCategory(string category)
{
    try
    {
        using var connection = new MySqlConnection("server=localhost;port=3306;database=book;user=root;password=123456;charset=utf8mb4;AllowPublicKeyRetrieval=True;");
        connection.Open();
        
        string sql;
        MySqlCommand command;
        
        if (category == "全部")
        {
            sql = "SELECT * FROM book_info;";
            command = new MySqlCommand(sql, connection);
        }
        else
        {
            sql = "SELECT * FROM book_info WHERE book_category = @Category;";
            command = new MySqlCommand(sql, connection);
            command.Parameters.AddWithValue("@Category", category);
        }
        
        MySqlDataAdapter adapter = new MySqlDataAdapter(command);
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);
        
        // 将查询结果绑定到DataGridView或其他控件
        dataGridView1.DataSource = dataTable;
        
        // 或者如果你使用的是其他控件，比如ListBox
        // listBox1.DataSource = dataTable;
        // listBox1.DisplayMember = "book_title"; // 假设有书名字段
    }
    catch (Exception ex)
    {
        MessageBox.Show($"查询图书时出错: {ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

// 窗体加载时调用
private void Form1_Load(object sender, EventArgs e)
{
    GetAllCategories();
    
    // 默认加载全部图书
    GetBooksByCategory("全部");
}

    private void DetAllBooks()
    {
        using var connection =
            new MySqlConnection(
                "server=localhost;port=3306;database=book;user=root;password=123456;charset=utf8mb4;AllowPublicKeyRetrieval=True;");
        connection.Open();
        string sql = "SELECT book_id,isbn,book_name,author FROM book_info ";
        MySqlDataAdapter adapter = new MySqlDataAdapter(sql, connection);
        DataSet dataSet = new DataSet();
        adapter.Fill(dataSet, "book_info");
        this.dataGridView1.DataSource = dataSet.Tables["book_info"];
    }
}
